<?php
/**
* @file $Id: ScheduleReport.php 161 2006-09-07 06:21:17Z doritojones $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

DrawHeader(ProgramTitle());
if($_REQUEST['subject_id'])
{
	$RET = DBGet(DBQuery("SELECT TITLE FROM COURSE_SUBJECTS WHERE SUBJECT_ID='".$_REQUEST['subject_id']."'"));
	$header .= "<A HREF=Modules.php?modname=$_REQUEST[modname]>"._('Top')."</A> >> <A HREF=Modules.php?modname=$_REQUEST[modname]&modfunc=courses&subject_id=$_REQUEST[subject_id]>".$RET[1]['TITLE'].'</A>';
	if($_REQUEST['course_id'])
	{
		$header2 = "<A HREF=Modules.php?modname=$_REQUEST[modname]&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]";
		$location = 'courses';
		$RET = DBGet(DBQuery("SELECT TITLE FROM COURSES WHERE COURSE_ID='".$_REQUEST['course_id']."'"));
		$header .= " >> <A HREF=Modules.php?modname=$_REQUEST[modname]&modfunc=course_weights&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]>".$RET[1]['TITLE'].'</A>';
		if($_REQUEST['course_weight'])
		{
			$header2 .= "&course_weight=$_REQUEST[course_weight]";
			$location = 'course_weights';
			$header .= " >> <A HREF=Modules.php?modname=$_REQUEST[modname]&modfunc=course_periods&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&course_weight=$_REQUEST[course_weight]>".$_REQUEST['course_weight'].'</A>';
			if($_REQUEST['course_period_id'])
			{
				$header2 .= "&course_period_id=$_REQUEST[course_period_id]";
				$location = 'course_periods';
				$RET = DBGet(DBQuery("SELECT TITLE FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'"));
				$header .= " >> <A HREF=Modules.php?modname=$_REQUEST[modname]&modfunc=students&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&course_weight=$_REQUEST[course_weight]&course_period_id=$_REQUEST[course_period_id]>".$RET[1]['TITLE'].'</A>';
			}
		}
		$header2 .= "&students=$location&modfunc=$location>"._('List Students')."</A> || ".$header2."&unscheduled=true&students=$location&modfunc=$location>"._('List Unscheduled Students')."</A>";
	
		DrawHeader($header,$header2);
	}
	else
		DrawHeader($header);
}

$LO_options = array('save'=>false,'search'=>false,'print'=>false);

echo '<TABLE><TR>';

// SUBJECTS ----
if(!$_REQUEST['modfunc'] || ($_REQUEST['modfunc']=='courses' && $_REQUEST['students']!='courses'))
{
	echo '<TD valign=top>';
	$QI = DBQuery("SELECT s.SUBJECT_ID,s.TITLE FROM COURSE_SUBJECTS s WHERE s.SYEAR='".UserSyear()."' AND s.SCHOOL_ID='".UserSchool()."'");
	$RET = DBGet($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));
	if(count($RET) && $_REQUEST['subject_id'])
	{
		foreach($RET as $key=>$value)
		{
			if($value['SUBJECT_ID']==$_REQUEST['subject_id'])
				$RET[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link['TITLE']['link'] = "Modules.php?modname=$_REQUEST[modname]&modfunc=courses";
	$link['TITLE']['variables'] = array('subject_id'=>'SUBJECT_ID');
	ListOutput($RET,array('TITLE'=>_('Subject')),_('Subject'),_('Subjects'),$link,array(),$LO_options);
	echo '</TD>';	
}

// COURSES ----
if($_REQUEST['modfunc']=='courses' || ($_REQUEST['modfunc']=='course_weights' && $_REQUEST['students']!='course_weights') || $_REQUEST['students']=='courses')
{
	echo '<TD valign=top>';
	$QI = DBQuery("SELECT c.COURSE_ID,c.TITLE,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as FILLED_SEATS,NULL AS OPEN_SEATS,(SELECT count(*) FROM SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID) AS COUNT_REQUESTS FROM COURSES c,COURSE_PERIODS cp WHERE c.SUBJECT_ID='$_REQUEST[subject_id]' AND c.COURSE_ID=cp.COURSE_ID AND c.SYEAR='".UserSyear()."' AND c.SCHOOL_ID='".UserSchool()."' GROUP BY c.COURSE_ID,c.TITLE");
	$RET = DBGet($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));
	if(count($RET) && $_REQUEST['course_id'])
	{
		foreach($RET as $key=>$value)
		{
			if($value['COURSE_ID']==$_REQUEST['course_id'])
				$RET[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link['TITLE']['link'] = "Modules.php?modname=$_REQUEST[modname]&modfunc=course_weights&subject_id=$_REQUEST[subject_id]";
	$link['TITLE']['variables'] = array('course_id'=>'COURSE_ID');
	ListOutput($RET,array('TITLE'=>_('Course'),'COUNT_REQUESTS'=>_('Requests'),'OPEN_SEATS'=>_('Open'),'TOTAL_SEATS'=>_('Total')),_('Course'),_('Courses'),$link,array(),$LO_options);
	echo '</TD>';
}

// COURSE WEIGHTS ----
if($_REQUEST['modfunc']=='course_weights' || ($_REQUEST['modfunc']=='course_periods' && $_REQUEST['students']!='course_periods') || $_REQUEST['students']=='course_weights')
{
	echo '<TD valign=top>';
	$QI = DBQuery("SELECT cp.COURSE_ID,cp.COURSE_WEIGHT,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as FILLED_SEATS,NULL AS OPEN_SEATS,(SELECT count(*) FROM SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=cp.COURSE_ID AND sr.COURSE_WEIGHT=cp.COURSE_WEIGHT) AS COUNT_REQUESTS FROM COURSE_PERIODS cp WHERE cp.COURSE_ID='".$_REQUEST['course_id']."' AND cp.SYEAR='".UserSyear()."' AND cp.SCHOOL_ID='".UserSchool()."' GROUP BY cp.COURSE_ID,cp.COURSE_WEIGHT");
	$RET = DBGet($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));
	if(count($RET) && $_REQUEST['course_weight'])
	{
		foreach($RET as $key=>$value)
		{
			if($value['COURSE_WEIGHT']==$_REQUEST['course_weight'])
				$RET[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link = array();
	$link['COURSE_WEIGHT']['link'] = "Modules.php?modname=$_REQUEST[modname]&modfunc=course_periods&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]";
	$link['COURSE_WEIGHT']['variables'] = array('course_weight'=>'COURSE_WEIGHT');
	ListOutput($RET,array('COURSE_WEIGHT'=>_('Weight'),'COUNT_REQUESTS'=>_('Requests'),'OPEN_SEATS'=>_('Open'),'TOTAL_SEATS'=>_('Total')),_('Course Weight'),_('Course Weights'),$link,array(),$LO_options);
	echo '</TD>';
}

// COURSE PERIODS ----
if($_REQUEST['modfunc']=='course_periods' || $_REQUEST['students']=='course_periods')
{
	echo '<TD valign=top>';
	$QI = DBQuery("SELECT cp.COURSE_ID,cp.COURSE_WEIGHT,cp.COURSE_PERIOD_ID,cp.TITLE,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as FILLED_SEATS,NULL AS OPEN_SEATS,cp.ROOM FROM COURSE_PERIODS cp WHERE cp.COURSE_ID='".$_REQUEST['course_id']."' AND cp.COURSE_WEIGHT='".$_REQUEST['course_weight']."' AND cp.SYEAR='".UserSyear()."' AND cp.SCHOOL_ID='".UserSchool()."' GROUP BY cp.COURSE_ID,cp.COURSE_WEIGHT,cp.COURSE_PERIOD_ID,cp.TITLE,cp.ROOM");
	$RET = DBGet($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));

	if(count($RET) && $_REQUEST['course_period_id'])
	{
		foreach($RET as $key=>$value)
		{
			if($value['COURSE_PERIOD_ID']==$_REQUEST['course_period_id'])
				$RET[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link = array();
	$link['TITLE']['link'] = "Modules.php?modname=$_REQUEST[modname]&modfunc=students&students=course_periods&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&course_weight=$_REQUEST[course_weight]";
	$link['TITLE']['variables'] = array('course_period_id'=>'COURSE_PERIOD_ID');
	ListOutput($RET,array('TITLE'=>_('Period - Teacher'),'ROOM'=>_('Room'),'OPEN_SEATS'=>_('Open'),'TOTAL_SEATS'=>_('Total')),_('Section'),_('Sections'),$link,array(),$LO_options);
	echo '</TD>';
}

// LIST STUDENTS ----
if($_REQUEST['students'])
{
	echo '<TD valign=top>';
	if($_REQUEST['unscheduled']=='true')
	{
		$sql = "SELECT s.LAST_NAME||', '||s.FIRST_NAME AS FULL_NAME,s.STUDENT_ID,s.CUSTOM_200000004,ssm.GRADE_ID 
				FROM SCHEDULE_REQUESTS sr,STUDENTS s,STUDENT_ENROLLMENT ssm
				WHERE (('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)) AND s.STUDENT_ID=sr.STUDENT_ID AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='".UserSyear()."' AND ssm.SCHOOL_ID='".UserSchool()."' ";
		if($_REQUEST['course_weight'] && $_REQUEST['course_id'])
			$sql .= "AND sr.COURSE_ID='$_REQUEST[course_id]' AND sr.COURSE_WEIGHT='$_REQUEST[course_weight]' ";
		elseif($_REQUEST['course_id'])
			$sql .= "AND sr.COURSE_ID='$_REQUEST[course_id]' ";
		$sql .= "AND NOT EXISTS (SELECT '' FROM SCHEDULE ss WHERE ss.COURSE_ID=sr.COURSE_ID AND ss.COURSE_WEIGHT=sr.COURSE_WEIGHT AND ss.STUDENT_ID=sr.STUDENT_ID AND ('".DBDate()."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL))";
	}
	else
	{
		$sql = "SELECT s.LAST_NAME||', '||s.FIRST_NAME AS FULL_NAME,s.STUDENT_ID,s.CUSTOM_200000004,ssm.GRADE_ID
				FROM SCHEDULE ss,STUDENTS s,STUDENT_ENROLLMENT ssm 
				WHERE ('".DBDate()."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL) AND (('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)) AND s.STUDENT_ID=ss.STUDENT_ID AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='".UserSyear()."' AND ssm.SCHOOL_ID='".UserSchool()."' ";
		if($_REQUEST['course_period_id'])
			$sql .= "AND ss.COURSE_PERIOD_ID='$_REQUEST[course_period_id]'";
		elseif($_REQUEST['course_weight'] && $_REQUEST['course_id'])
			$sql .= "AND ss.COURSE_ID='$_REQUEST[course_id]' AND ss.COURSE_WEIGHT='$_REQUEST[course_weight]'";
		elseif($_REQUEST['course_id'])
			$sql .= "AND ss.COURSE_ID='$_REQUEST[course_id]'";	
	}
	$RET = DBGet(DBQuery($sql),array('CUSTOM_200000004'=>'ShortDate','GRADE_ID'=>'GetGrade'));
	
	$link = array();
	$link['FULL_NAME']['link'] = "Modules.php?modname=Scheduling/Schedule.php";
	$link['FULL_NAME']['variables'] = array('student_id'=>'STUDENT_ID');
	ListOutput($RET,array('FULL_NAME'=>_('Student'),'GRADE_ID'=>_('Grade'),'CUSTOM_200000004'=>_('Birthdate')),_('Student'),_('Students'),$link,array(),$LO_options);
	echo '</TD>';
}

echo '</TR></TABLE>';

function _calcOpenSeats($null)
{	global $THIS_RET;
	
	return $THIS_RET['TOTAL_SEATS'] - $THIS_RET['FILLED_SEATS'];
}
?>